<?php

namespace app\admin\controller\dormitory;

use app\admin\model\DormitoryBedExchange;
use app\admin\model\DormitoryDorm;
use app\admin\model\DormitoryDormBed;
use app\admin\model\DormitoryGarden;
use app\common\controller\Backend;
use app\common\library\Auth;
use app\common\model\BehaviorRecordDetail;
use app\common\model;
use fast\Random;
use think\Db;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use think\Exception;
use think\exception\PDOException;

class Student extends Backend
{
    /**
     * @var \app\admin\model\Student
     */
    protected $model = null;
    protected $searchFields = 'name';
    protected $noNeedRight = ['genderList', 'check', 'record', 'download', 'import', 'detail'];
    protected $genderData = []; //性别
    protected $facultyData = []; //院系
    protected $majorData = []; //专业

    public function _initialize()
    {
        parent::_initialize();
        $this->model = model('Student');

        //性别
        $this->genderData = model\User::$genderList;

        //院系
        $result = model\Faculty::all();
        foreach ($result as $key => $value) {
            $this->facultyData[$value['id']] = $value['name'];
        }

        //专业
        $result = model\Major::all();
        foreach ($result as $key => $value) {
            $this->majorData[$value['id']] = $value['name'];
        }

        $this->view->assign('genderData', $this->genderData);//性别
        $this->view->assign('facultyData', $this->facultyData);//院系
        $this->view->assign('majorData', $this->majorData);//专业
    }

    /**
     * 查看
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {

            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }

            list($where, $sort, $order, $offset, $limit) = $this->buildparams();

            $total = $this->model
                ->alias('a')
                ->join('__DORMITORY_DORM_BED__ b', 'b.student_id = a.id', 'left')
                ->join('__DORMITORY_GARDEN__ garden', 'garden.number = b.garden_number', 'left')
                ->join('__DORMITORY_BUILDING__ building', 'building.number = b.building_number and building.garden_number = b.garden_number', 'left')
                ->join('__BASE_FACULTY__ d', 'd.id = a.faculty_id', 'left')
                ->join('__USER__ user', 'user.target_id = a.id and user.target_type = 1', 'left')
                ->join('__USER_GROUP__ group', 'group.id = user.group_id', 'left')
                ->join('__BASE_CLASSES__ classes', 'classes.id = a.class_id', 'left')
                ->where($where)
                ->count();

            $list = $this->model
                ->alias('a')
                ->join('__DORMITORY_DORM_BED__ b', 'b.student_id = a.id', 'left')
                ->join('__DORMITORY_GARDEN__ garden', 'garden.number = b.garden_number', 'left')
                ->join('__DORMITORY_BUILDING__ building', 'building.number = b.building_number and building.garden_number = b.garden_number', 'left')
                ->join('__BASE_FACULTY__ d', 'd.id = a.faculty_id', 'left')
                ->join('__USER__ user', 'user.target_id = a.id and user.target_type = 1', 'left')
                ->join('__USER_GROUP__ group', 'group.id = user.group_id', 'left')
                ->join('__BASE_CLASSES__ classes', 'classes.id = a.class_id', 'left')
                ->where($where)
                ->field('a.id,a.number,a.name,a.id_number,a.gender,a.created_at,a.is_bed,a.score,b.num,b.number AS bed_number,b.dorm_number dorm_name,garden.name garden_name,building.name building_name,d.name AS faculty_name,group.name group_name,classes.name class_name,classes.grade_name')
                ->order($sort, $order)
                ->limit($offset, $limit)
                ->select();

            if ($list) {
                $list = collection($list)->append(['gender_text'])->toArray();
            }

            $result = array("total" => $total, "rows" => $list);

            return json($result);
        }
        return $this->view->fetch();
    }

    /**
     * 添加
     */
    public function add()
    {
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");

            if ($params) {

                $params['creator_id'] = $this->auth->id;

                $group_id = $this->request->post('group_id');
                $group_type = $this->request->post('group_type', 1);
                $group_end_time = $this->request->post('group_end_time');
                $bed_id = $this->request->post("bed_id");

                if ($bed_id) {
                    $bed = DormitoryDormBed::find($bed_id);

                    if (!$bed)
                        $this->error('所选床位不存在');

                    if ($bed['status'] != DormitoryDormBed::XIANZHI)
                        $this->error('所选床位不是闲置状态,不能分配');
                }

                $validate = Validate('Student');
                $res = $validate->scene('add')->check($params);
                if (!$res) {
                    $this->error($validate->getError());
                }

                if ($group_id && $group_type == '2' && !$group_end_time)
                    $this->error('请选择到期时间');

                // 启动事务
                Db::startTrans();
                try {

                    if (isset($bed)) {
                        $params['is_bed'] = 1;

                        $result = $this->model->create($params);

                        $bed->student_id = $result->id;
                        $bed->status = DormitoryDormBed::ZAIYONG;
                        $bed->save();

                        DormitoryDorm::where('number', $bed['dorm_number'])
                            ->where('garden_number', $bed['garden_number'])
                            ->where('building_number', $bed['building_number'])
                            ->setInc('bed_used_count', 1);

                    } else {
                        $result = $this->model->create($params);
                    }

                    $auth = new Auth();
                    $extend['target_id'] = $result->id;
                    $extend['target_type'] = model\User::STUDENT;
                    $extend['nickname'] = $params['name'];
                    $extend['gender'] = $params['gender'];
                    if ($group_id) {
                        $extend['group_id'] = $group_id;
                        if ($group_type == 2) {
                            $extend['group_end_time'] = strtotime($group_end_time);
                        } else {
                            $extend['group_end_time'] = 0;
                        }
                    }
                    $auth->register($params['number'], substr($params['number'], -6), '', '', $extend);

                    // 提交事务
                    Db::commit();
                } catch (\Exception $e) {
                    // 回滚事务
                    Db::rollback();
                    $this->error($e->getMessage() ? $e->getMessage() : __('添加失败'));
                }

                $this->success();
            }
            $this->error();
        }

        $User_group = model\UserGroup::column('id,name');
        $group[0] = '请选择';
        foreach ($User_group as $key => $value) {
            $group[$key] = $value;
        }
        $this->view->assign('groupList', build_select('group_id', $group, null, ['class' => 'form-control selectpicker']));
        $group_type = ['0' => '请选择', '1' => '长期', '2' => '短期'];
        $this->view->assign('groupTypeList', build_select('group_type', $group_type, null, ['class' => 'form-control selectpicker group_type']));

        return $this->view->fetch();
    }

    /**
     * 编辑
     */
    public function edit($ids = null)
    {
        $row = $this->model->with('bed')->where('id', $ids)->find();
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        $user = model\User::where('target_id', $row['id'])
            ->where('target_type', model\User::STUDENT)
            ->field('id,group_id,group_end_time')
            ->find();

        if ($this->request->isPost()) {
            $params = $this->request->post("row/a", [], 'strip_tags');
            if ($params) {

                $validate = Validate('Student');
                $res = $validate->scene('edit')->check($params);
                if (!$res) {
                    $this->error($validate->getError());
                }

                $group_id = $this->request->post('group_id');
                $group_type = $this->request->post('group_type', 1);
                $group_end_time = $this->request->post('group_end_time');
                $bed_id = $this->request->post('bed_id');

                if ($bed_id) {
                    $new_bed = DormitoryDormBed::get($bed_id);
                    if (!$new_bed) $this->error('床位不存在');

                    if (!$row['is_bed'] && $new_bed['status'] > 1)
                        $this->error('该床位已经有人了, 无法调换');

                    $dorm = DormitoryDorm::where('number', $new_bed['dorm_number'])
                        ->where('garden_number', $new_bed['garden_number'])
                        ->where('building_number', $new_bed['building_number'])
                        ->field('type')
                        ->find();

                    if (!$dorm) $this->error('您选择的寝室不存在');

                    if ($dorm['type'] != $params['gender']) $this->error('您选择的寝室类型与该学生性别不符');
                }

                if ($group_id && $group_type == 2 && !$group_end_time)
                    $this->error('请选择到期时间');

                // 启动事务
                Db::startTrans();
                try {

                    if (isset($new_bed)) {

                        // 该学生是否已分配床位
                        if ($row['is_bed']) {

                            if ($ids != $new_bed['student_id']) {
                                $old_bed = DormitoryDormBed::get(['student_id' => $ids]);

                                // 新床位是否有人
                                if ($new_bed['status'] > 1) {
                                    // 有人 两个人调换床位
                                    $old_bed->student_id = $new_bed['student_id'];
                                    $old_bed->save();
                                    $old_exchange = new DormitoryBedExchange();
                                    $old_exchange->student_id = $new_bed['student_id'];
                                    $old_exchange->old_bed_id = $new_bed['id'];
                                    $old_exchange->new_bed_id = $old_bed['id'];
                                    $old_exchange->save();

                                    $new_bed->student_id = $ids;
                                    $new_bed->save();
                                    $new_exchange = new DormitoryBedExchange();
                                    $new_exchange->student_id = $ids;
                                    $new_exchange->old_bed_id = $old_bed['id'];
                                    $new_exchange->new_bed_id = $new_bed['id'];
                                    $new_exchange->save();

                                } else {
                                    // 没人 换到新床位 把旧床位置空
                                    $old_bed->student_id = 0;
                                    $old_bed->status = DormitoryDormBed::XIANZHI;
                                    $old_bed->save();

                                    $new_bed->student_id = $ids;
                                    $new_bed->status = DormitoryDormBed::ZAIYONG;
                                    $new_bed->save();

                                    $exchange = new DormitoryBedExchange();
                                    $exchange->student_id = $ids;
                                    $exchange->old_bed_id = $old_bed['id'];
                                    $exchange->new_bed_id = $new_bed['id'];
                                    $exchange->save();

                                    DormitoryDorm::where('number', $old_bed['dorm_number'])
                                        ->where('garden_number', $old_bed['garden_number'])
                                        ->where('building_number', $old_bed['building_number'])
                                        ->setDec('bed_used_count', 1);

                                    DormitoryDorm::where('number', $new_bed['dorm_number'])
                                        ->where('garden_number', $new_bed['garden_number'])
                                        ->where('building_number', $new_bed['building_number'])
                                        ->setInc('bed_used_count', 1);

                                }
                            }

                        } else {

                            // 新床位是否有人
                            if ($new_bed['status'] > 1) {
                                $this->error('该床位已经有人了, 无法调换');

                            } else {
                                // 没人
                                $new_bed->student_id = $ids;
                                $new_bed->status = DormitoryDormBed::ZAIYONG;
                                $new_bed->save();

                                $params['is_bed'] = 1;

                                DormitoryDorm::where('number', $new_bed['dorm_number'])
                                    ->where('garden_number', $new_bed['garden_number'])
                                    ->where('building_number', $new_bed['building_number'])
                                    ->setInc('bed_used_count', 1);

                            }
                        }

                    }

                    $row->save($params);

                    if ($user && $group_id) {

                        if ($group_id) {
                            $user->group_id = $group_id;

                            if ($group_type == 2) {
                                $user->group_end_time = strtotime($group_end_time);
                            } else {
                                $user->group_end_time = 0;
                            }
                        }

                        $user->gender = $params['gender'];
                        $user->nickname = $params['name'];
                        $user->username = $params['number'];

                        $user->save();
                    }

                    // 提交事务
                    Db::commit();
                } catch (\Exception $e) {
                    // 回滚事务
                    Db::rollback();
                    $this->error($e->getMessage() ? $e->getMessage() : __('保存失败'));
                }


                $this->success();
            }
            $this->error();
        }

        $User_group = model\UserGroup::column('id,name');

        //array_unshift($group, '请选择');
        $group[0] = '请选择';
        foreach ($User_group as $key => $value) {
            $group[$key] = $value;
        }

        $this->view->assign('groupList', build_select('group_id', $group, $user['group_id'], ['class' => 'form-control selectpicker']));

        $row['group_type'] = 0;
        $row['group_end_time'] = '';
        if ($user['group_end_time'] === 0) {
            $row['group_type'] = 1;
        } else if ($user['group_end_time'] > 0) {
            $row['group_type'] = 2;
            $row['group_end_time'] = date('Y-m-d H:i:s', $user['group_end_time']);
        }
        $group_type = ['0' => '请选择', '1' => '长期', '2' => '短期'];
        $this->view->assign('groupTypeList', build_select('group_type', $group_type, $row['group_type'], ['class' => 'form-control selectpicker group_type']));

        $grade = model\Grade::alias('grade')
            ->join('__BASE_CLASSES__ class', 'class.grade_id = grade.id')
            ->where('class.id', $row['class_id'])
            ->field('grade.id grade_id,class.id class_id')
            ->find();
        $row['grade_id'] = @$grade['grade_id'];
        $row['class_id'] = @$grade['class_id'];
        $this->view->assign("row", $row);

        return $this->view->fetch();
    }

    public function genderList()
    {
        $result = $this->genderData;
        $searchList = [];
        foreach ($result as $key => $value) {
            $searchList[] = ['id' => $key, 'name' => $value];
        }
        $data = ['searchlist' => $searchList];
        $this->success('', null, $data);
    }

    public function check()
    {
        $student_id = $this->request->post('student_id');
        $bed_id = $this->request->post('bed_id');

        $student = $this->model->get($student_id);
        if (!$student) $this->error('学生不存在');

        $bed = DormitoryDormBed::get($bed_id);
        if (!$bed) $this->error('床位不存在');

        // 该学生是否已分配床位
        if ($student['is_bed']) {
            // 有床位
            if ($bed['status'] > 1 && $student_id != $bed['student_id']) {
                $this->success('此床位已有人,保存后将调换床位');
            }
        } else {
            // 没有分配
            if ($bed['status'] > 1 && $student_id != $bed['student_id']) {
                $this->error('该床位已经有人了, 无法调换');
            }
        }

        $this->success();
    }

    public function ranking()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {

            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }

            list($where, $sort, $order, $offset, $limit) = $this->buildparams();

            $total = $this->model
                ->alias('a')
                ->join('__BASE_FACULTY__ d', 'd.id = a.faculty_id', 'left')
                ->where($where)
                ->count();

            $list = $this->model
                ->alias('a')
                ->join('__BASE_FACULTY__ d', 'd.id = a.faculty_id', 'left')
                ->where($where)
                ->field('a.id,a.number,a.gender,a.name,a.score,d.name AS faculty_name')
                ->order('score', 'desc')
                ->limit($offset, $limit)
                ->select();

            if ($list) {
                $list = collection($list)->append(['gender_text'])->toArray();

                // 第一个是分数最大的
                $max = current($list);

                // 获取比当前最大分分数高的人数
                $count = \app\admin\model\Student::where('score', '>', $max['score'])->count();

                $sort = 1;
                foreach ($list as $key => $value) {
                    $list[$key]['num'] = $offset + $key + 1;

                    //数组中第一个是当前分数最高的
                    if ($key == 0) {
                        $list[$key]['sort'] = $count + $sort;
                    } else {
                        // 继续循环
                        // 如果当前的分数与上一个的相等 那么就与上一个的排名并列
                        if ($list[$key - 1]['score'] == $value['score']) {
                            $list[$key]['sort'] = $list[$key - 1]['sort'];
                        } else {
                            // 如果不等 那就看看有没有分页情况
                            // 如果有分页 也就是说之前的所有的分数都大于当前的分数 排名累加上
                            $page_count = 0;
                            if ($offset > 0)
                                $page_count = ($offset / $limit) * $limit;
                            $list[$key]['sort'] = $page_count + $sort;
                        }
                    }

                    $sort++;
                }
            }

            $result = array("total" => $total, "rows" => $list);

            return json($result);
        }
        return $this->view->fetch();
    }

    public function record()
    {
        $student_id = $this->request->request('student_id');
        $this->assignconfig("student_id", $student_id);

        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {

            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }

            list($where, $sort, $order, $offset, $limit) = $this->buildparams();

            $total = BehaviorRecordDetail::alias('a')
                ->join('__BEHAVIOR_RECORD__ b', 'b.id = a.record_id', 'left')
                ->where($where)
                ->where('b.student_id', $student_id)
                ->count();

            $list = BehaviorRecordDetail::alias('a')
                ->join('__BEHAVIOR_RECORD__ b', 'b.id = a.record_id', 'left')
                ->where($where)
                ->where('b.student_id', $student_id)
                ->field('a.id,b.record_user_group,b.created_at,b.student_id,a.score,a.behavior_name')
                ->order($sort, $order)
                ->limit($offset, $limit)
                ->select();

            $result = array("total" => $total, "rows" => $list);

            return json($result);
        }
        return $this->view->fetch();
    }

    public function delRecord($ids = "")
    {
        if ($ids) {
            $ids = explode(',', $ids);
            $ids = array_unique($ids);

            $record_detail_list = BehaviorRecordDetail::where('id', 'in', $ids)->select();

            foreach ($record_detail_list as $key => $value) {

                Db::startTrans();
                try {
                    $record = model\BehaviorRecord::where('id', $value['record_id'])->find();

                    if (!$record) continue;

                    $dorm = DormitoryDorm::where('garden_number', $record['garden_number'])
                        ->where('building_number', $record['building_number'])
                        ->where('number', $record['dorm_number'])
                        ->find();

                    if (!$dorm) continue;

                    // 判断删除的事学生分 还是 寝室分
                    if ($record['student_id']) {

                        $result = model\BehaviorRecord::alias('a')
                            ->join('__BEHAVIOR_RECORD_DETAIL__ b', 'b.record_id = a.id')
                            ->where('a.garden_number', $record['garden_number'])
                            ->where('a.building_number', $record['building_number'])
                            ->where('a.dorm_number', $record['dorm_number'])
                            ->where('a.student_id', '>', 0)
                            ->where('a.id', $record['id'])
                            ->field('COUNT(DISTINCT(a.student_id)) student_count,SUM(b.score) student_score')
                            ->find();

                        $student_count = $result['student_count'] ? $result['student_count'] : 0;
                        $student_score = $result['student_score'] ? $result['student_score'] : 0;

                        $score = 0;
                        if ($student_count)
                            $score = (float)number_format(($student_score - $value['score']) / $student_count, 2, '.', '');

                        $dorm->total_score = $dorm['score'] + $score;
                        $dorm->save();

                        $this->model->where('id', $record['student_id'])->setDec('score', $value['score']);

                    } else {

                        $dorm->setDec('score', $value['score']);
                        $dorm->setDec('total_score', $value['score']);
                    }

                    BehaviorRecordDetail::where('id', $value['id'])->delete();

                    // 提交事务
                    Db::commit();

                } catch (\Exception $e) {
                    // 回滚事务
                    Db::rollback();

                    $this->error($e->getMessage() ? $e->getMessage() : '删除失败');
                }
            }

            $this->success();
        }

        $this->error();
    }

    public function detail()
    {
        $ids = $this->request->request('ids', null);
        $row = $this->model->with('bed')->where('id', $ids)->find();
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        $user = model\User::where('target_id', $row['id'])
            ->where('target_type', model\User::STUDENT)
            ->field('id,group_id,group_end_time')
            ->find();

        $User_group = model\UserGroup::column('id,name');

        $group[0] = '请选择';
        foreach ($User_group as $key => $value) {
            $group[$key] = $value;
        }

        $this->view->assign('groupList', build_select('group_id', $group, $user['group_id'], ['class' => 'form-control selectpicker', 'disabled']));

        $row['group_type'] = 0;
        $row['group_end_time'] = '';
        if ($user['group_end_time'] === 0) {
            $row['group_type'] = 1;
        } else if ($user['group_end_time'] > 0) {
            $row['group_type'] = 2;
            $row['group_end_time'] = date('Y-m-d H:i:s', $user['group_end_time']);
        }
        $group_type = ['0' => '请选择', '1' => '长期', '2' => '短期'];
        $this->view->assign('groupTypeList', build_select('group_type', $group_type, $row['group_type'], ['class' => 'form-control selectpicker group_type', 'disabled']));

        $grade = model\Grade::alias('grade')
            ->join('__BASE_CLASSES__ class', 'class.grade_id = grade.id')
            ->where('class.id', $row['class_id'])
            ->field('grade.id grade_id,class.id class_id')
            ->find();
        $row['grade_id'] = @$grade['grade_id'];
        $row['class_id'] = @$grade['class_id'];
        $this->view->assign("row", $row);

        return $this->view->fetch();
    }

    public function download()
    {
        $file_path = 'template/学生信息.xls';
        $file_dir = ROOT_PATH . 'public' . DS . 'uploads' . DS . "$file_path";    // 下载文件存放目录

        // 检查文件是否存在
        if (!file_exists($file_dir)) {
            $this->error('文件不存在');
        } else {
            // 打开文件
            $file1 = fopen($file_dir, "r");
            // 输入文件标签
            Header("Content-type: application/octet-stream");
            Header("Accept-Ranges: bytes");
            Header("Accept-Length:" . filesize($file_dir));
            Header("Content-Disposition: attachment;filename=学生信息.xls");
            ob_clean();
            flush();
            echo fread($file1, filesize($file_dir));
            fclose($file1);
            exit();
        }
    }

    public function import()
    {
        if ($this->request->isAjax()) {

            set_time_limit(0);
            ini_set('memory_limit', '512M');
            ini_set('max_execution_time', '0');
            $file = $this->request->request('file');

            if (!$file) {
                $this->error(__('Parameter %s can not be empty', 'file'));
            }
            $filePath = ROOT_PATH . DS . 'public' . DS . $file;
            if (!is_file($filePath)) {
                $this->error(__('No results were found'));
            }
            //实例化reader
            $ext = pathinfo($filePath, PATHINFO_EXTENSION);
            if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
                $this->error(__('Unknown data format'));
            }
            if ($ext === 'csv') {
                $file = fopen($filePath, 'r');
                $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
                $fp = fopen($filePath, "w");
                $n = 0;
                while ($line = fgets($file)) {
                    $line = rtrim($line, "\n\r\0");
                    $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                    if ($encoding != 'utf-8') {
                        $line = mb_convert_encoding($line, 'utf-8', $encoding);
                    }
                    if ($n == 0 || preg_match('/^".*"$/', $line)) {
                        fwrite($fp, $line . "\n");
                    } else {
                        fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                    }
                    $n++;
                }
                fclose($file) || fclose($fp);

                $reader = new Csv();
            } elseif ($ext === 'xls') {
                $reader = new Xls();
            } else {
                $reader = new Xlsx();
            }

            //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
            $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';

            $table = $this->model->getQuery()->getTable();
            $database = \think\Config::get('database.database');
            $fieldArr = [];
            $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
            foreach ($list as $k => $v) {
                if ($importHeadType == 'comment') {
                    $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
                } else {
                    $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
                }
            }

            //dd($fieldArr);

            //加载文件
            $insert = [];

            try {
                if (!$PHPExcel = $reader->load($filePath)) {
                    $this->error(__('Unknown data format'));
                }
                $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
                $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
                $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
                $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
                $fields = [];
                for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                    for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                        $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                        $fields[] = $val;
                    }
                }

                $creator_id = $this->auth->id;

                for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                    $values = [];
                    for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                        $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();

                        $values[] = is_null($val) ? '' : $val;
                    }
                    $row = [];
                    $temp = array_combine($fields, $values);

                    //dd(compact('fieldArr', 'fields', 'temp'));

                    // 专业
                    $major = [
                        'name' => @$temp['专业'],
                        'code' => @$temp['专业代码'],
                        'international_code' => @$temp['国标专业代码'],
                        'direction' => @$temp['专业方向'],
                        'level' => @$temp['专业培养层次'],
                        'creator_id' => $creator_id,
                    ];
                    // 班级
                    $classes = [
                        'grade_name' => isset($temp['年级']) ? $temp['年级'] : @$temp['现在年级'],
                        'system' => @$temp['学制']
                    ];
                    // 辅导员
                    $teacher = [
                        'name' => @$temp['辅导员'],
                        'mobile' => @$temp['辅导员联系方式'],
                        'creator_id' => $creator_id,
                    ];
                    // 联系地址
                    $address = [
                        'address' => @$temp['联系地址'],
                        'contacts' => @$temp['联系人'],
                        'telephone' => @$temp['联系电话'],
                        'mobile' => @$temp['手机号码'],
                    ];

                    foreach ($temp as $k => $v) {
                        if ($k !== '' && isset($fieldArr[$k])) {

                            switch ($fieldArr[$k]) {
                                case 'gender':
                                    // 性别
                                    $v = $this->gender($v);
                                    break;
                                case 'faculty_id':
                                    // 院(系)/部
                                    $v = $this->faculty($v);
                                    break;
                                case 'subject_id':
                                    // 学科
                                    $v = $this->subject($v);
                                    break;
                                case 'major_id':
                                    // 专业
                                    $v = $this->major($major);
                                    break;
                                case 'class_id':
                                    // 班级
                                    $v = $this->classes($v, $classes);
                                    break;
                                case 'instructor_teacher_id':
                                    // 辅导员
                                    $v = $this->teacher($v, $teacher);
                                    break;
                                case 'school_status':
                                    // 在校状态
                                    $v = $this->schoolStatus($v);
                                    break;
                                case 'student_status':
                                    // 学籍状态
                                    $v = $this->studentStatus($v);
                                    break;
                                case 'nation_id':
                                    // 民族
                                    $v = $this->nation($v);
                                    break;
                                case 'address_id':
                                    // 联系地址
                                    $v = $this->address($address);
                                    break;
                                case 'political_appearance_id':
                                    // 政治面貌
                                    $v = $this->politicalAppearance($v);
                                    break;
                                case 'examination_category_id':
                                    // 考生类别
                                    $v = $this->examinationCategory($v);
                                    break;
                                case 'garden_id':
                                    // 校区
                                    $v = $this->garden($v);
                                    break;
                            }

                            $row[$fieldArr[$k]] = $v;
                        }
                    }

                    if ($row) {
                        $row['creator_id'] = $creator_id;
                        $insert[] = $row;
                    }
                }

            } catch (Exception $exception) {
                $this->error($exception->getMessage());
            }

            if (!$insert) {
                $this->error(__('No rows were updated'));
            }

            $auth = new Auth();

            $limit = 500;
            $count = ceil(count($insert) / $limit);
            for ($i = 1; $i <= $count; $i++) {
                $offset = ($i - 1) * $limit;
                $data = array_slice($insert, $offset, $limit);

                // 启动事务
                Db::startTrans();
                try {

                    $student_result = $this->model->saveAll($data);

                    $user_data = [];
                    foreach ($student_result as $key => $value) {

                        $ip = request()->ip();
                        $time = time();

                        $params = [
                            'username' => $value['number'],
                            'nickname' => isset($value['name']) ? $value['name'] : $value['number'],
                            'gender' => $value['gender'],
                            'birthday' => $value['birthday'],
                            'salt' => Random::alnum(),
                            'jointime' => $time,
                            'joinip' => $ip,
                            'logintime' => $time,
                            'loginip' => $ip,
                            'prevtime' => $time,
                            'status' => 'normal',
                            'level' => 1,
                            'score' => 0,
                            'avatar' => '',
                            'target_id' => @$value['id'],
                            'target_type' => 1,
                        ];
                        if (strlen($value['number']) >= 6) $password = substr($value['number'], -6);
                        else $password = $value['number'];
                        $params['password'] = $auth->getEncryptPassword($password, $params['salt']);

                        $user_data[] = $params;
                    }

                    $user = new model\User;
                    $user->saveAll($user_data);

                    // 提交事务
                    Db::commit();
                } catch (PDOException $exception) {

                    // 回滚事务
                    Db::rollback();

                    $msg = $exception->getMessage();
                    if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
                        $msg = "导入失败，学号【{$matches[1]}】已存在";
                    };
                    $this->error($msg);
                } catch (\Exception $e) {
                    // 回滚事务
                    Db::rollback();
                    $this->error($e->getMessage() ? $e->getMessage() : __('添加失败'));
                }
            }

            $this->success();
        }

        return $this->view->fetch();


    }

    // 性别
    private function gender($v)
    {
        return $v == '男' ? 1 : ($v == '女' ? 2 : 3);
    }

    // 院(系)/部
    private function faculty($v)
    {
        if (!$v) return 0;

        $cache_key = 'faculty_' . $v;
        if (cache($cache_key)) {
            return cache($cache_key);
        } else {
            $faculty = model\Faculty::where('name', $v)->find();
            if ($faculty) {
                $faculty_id = $faculty['id'];
            } else {
                $faculty = new model\Faculty();
                $faculty->data([
                    'name' => $v,
                    'creator_id' => $this->auth->id
                ]);
                $faculty->save();
                $faculty_id = $faculty->id;
            }

            cache($cache_key, $faculty_id);
            return $faculty_id;
        }
    }

    // 学科
    private function subject($v)
    {
        if (!$v) return 0;

        $cache_key = 'subject_' . $v;
        if (cache($cache_key)) {
            return cache($cache_key);
        } else {
            $subject = model\Subject::where('name', $v)->find();
            if ($subject) {
                $subject_id = $subject['id'];
            } else {
                $subject = new model\Subject();
                $subject->data([
                    'name' => $v,
                    'creator_id' => $this->auth->id
                ]);
                $subject->save();
                $subject_id = $subject->id;
            }
            cache($cache_key, $subject_id);
            return $subject_id;
        }
    }

    // 专业
    private function major($data)
    {
        if (!$data['name']) return 0;
        $cache_key = 'major_' . $data['name'];
        if (cache($cache_key)) {
            return cache($cache_key);
        } else {
            $major = model\Major::where('name', $data['name'])->find();
            if ($major) {
                $major_id = $major['id'];
            } else {
                $major = new model\Major();
                $major->data($data);
                $major->save();

                $major_id = $major->id;
            }

            cache($cache_key, $major_id);
            return $major_id;
        }
    }

    // 班级
    private function classes($v, $data)
    {
        if (!$v || !$data['grade_name']) return 0;

        $cache_key = 'classes_' . $v . $data['grade_name'];
        if (cache($cache_key)) {
            return cache($cache_key);
        } else {
            $classes = model\Classes::where('name', $v)->where('grade_name', $data['grade_name'])->find();
            if ($classes) {
                $classes_id = $classes['id'];
            } else {
                $cache_grade_key = 'grade_' . $data['grade_name'];
                if (cache($cache_grade_key)) {
                    $grade_id = cache($cache_grade_key);
                } else {
                    $grade = model\Grade::where('name', $data['grade_name'])->find();
                    if ($grade) {
                        $grade_id = $grade['id'];
                    } else {
                        $grade = new model\Grade;
                        $grade->name = $data['grade_name'];
                        $grade->creator_id = $this->auth->id;
                        $grade->save();
                        $grade_id = $grade->id;
                    }
                }

                $classes = new model\Classes();
                $classes->name = $v;
                $classes->grade_id = $grade_id;
                $classes->grade_name = $data['grade_name'];
                $classes->system = @$data['system'];
                $classes->creator_id = $this->auth->id;

                $classes->save();

                $classes_id = $classes->id;

            }

            cache($cache_key, $classes_id);

            return $classes_id;
        }

    }

    // 辅导员
    private function teacher($v, $data)
    {
        if (!$data['name']) return 0;

        $cache_key = 'teacher_' . $v;
        if (cache($cache_key)) {
            return cache($cache_key);
        } else {
            $teacher = model\Teacher::where('name', $v)->find();
            if ($teacher) {
                $teacher_id = $teacher['id'];
            } else {
                $teacher = new model\Teacher();
                $teacher->data($data);
                $teacher->save();
                $teacher_id = $teacher->id;
            }
            cache($cache_key, $teacher_id);
            return $teacher_id;
        }
    }

    // 在校状态
    private function schoolStatus($v)
    {
        return $v == '在校' ? 1 : 0;
    }

    // 学籍状态
    private function studentStatus($v)
    {
        return $v == '有' ? 1 : 0;
    }

    // 民族
    private function nation($v)
    {
        if (!$v) return 0;

        $cache_key = 'nation_' . $v;
        if (cache($cache_key)) {
            return cache($cache_key);
        } else {
            $nation = model\Nation::where('name', $v)->find();
            if ($nation) {
                $nation_id = $nation['id'];
            } else {
                $nation = new model\Nation();
                $nation->name = $v;
                $nation->save();

                $nation_id = $nation->id;
            }

            cache($cache_key, $nation_id);
            return $nation_id;
        }
    }

    // 联系地址
    private function address($data)
    {
        if (!$data['address']) return 0;

        $cache_key = 'address_' . $data['address'] . $data['contacts'];
        if (cache($cache_key)) {
            return cache($cache_key);
        } else {
            $address = model\Address::where('address', $data['address'])->where('contacts', $data['contacts'])->find();
            if ($address) {
                $address_id = $address['id'];
            } else {
                $address = new model\Address;
                $address->data($data);
                $address->save();

                $address_id = $address->id;
            }

            cache($cache_key, $address_id);

            return $address_id;
        }
    }

    // 政治面貌
    private function politicalAppearance($v)
    {
        if (!$v) return 0;

        $cache_key = 'politicalAppearance_' . $v;
        if (cache($cache_key)) {
            return cache($cache_key);
        } else {
            $politicalAppearance = model\PoliticalAppearance::get(['name' => $v]);
            if ($politicalAppearance) {
                $political_appearance_id = $politicalAppearance['id'];
            } else {
                $politicalAppearance = new model\PoliticalAppearance;
                $politicalAppearance->name = $v;
                $politicalAppearance->creator_id = $this->auth->id;
                $politicalAppearance->save();

                $political_appearance_id = $politicalAppearance->id;
            }
            cache($cache_key, $political_appearance_id);
            return $political_appearance_id;
        }
    }

    // 考生类别
    private function examinationCategory($v)
    {
        if (!$v) return 0;

        $cache_key = 'examinationCategory_' . $v;
        if (cache($cache_key)) {
            return cache($cache_key);
        } else {
            $examinationCategory = model\ExaminationCategory::get(['name' => $v]);
            if ($examinationCategory) {
                $examination_category_id = $examinationCategory['id'];
            } else {
                $examinationCategory = new model\ExaminationCategory();
                $examinationCategory->name = $v;
                $examinationCategory->creator_id = $this->auth->id;
                $examinationCategory->save();

                $examination_category_id = $examinationCategory->id;
            }
            cache($cache_key, $examination_category_id);
            return $examination_category_id;
        }
    }

    // 园区
    private function garden($v)
    {
        if (!$v) return 0;
        $cache_key = 'garden_' . $v;
        if (cache($cache_key)) {
            return cache($cache_key);
        } else {
            $garden = DormitoryGarden::get(['name' => $v]);
            if ($garden) {
                return $garden['id'];
            } else {
                return 0;
            }
        }
    }

}
